package com.newbsd.web.maventest.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.newbsd.web.maventest.pojo.Ticket;
import com.newbsd.web.maventest.service.UserQueryDao;

public class UserQueryDaoImpl implements UserQueryDao {

	@Override
	public List<Ticket> QueryTickets(String origin, String destination,int pagesize,int rows) {
		Connection conn = null;
		PreparedStatement ps = null;
		Ticket ticket = null;
		List<Ticket> ticketlist =new ArrayList<>();
		
		try {
			Context context = new InitialContext();
			DataSource datasource = (DataSource)context.lookup("java:comp/env/jdbc/maventest");
			conn = datasource.getConnection();
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}
		
		if (null != conn) {
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement("select * from ticket where origin=? and destination=?  limit ?,?");
				ps.setString(1, origin);
				ps.setString(2, destination);
				ps.setInt(3, (pagesize-1)*rows);
				ps.setInt(4, rows);

				// 执行
				rs = ps.executeQuery();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			if (null != rs) {
				try {
					while(rs.next()) {
						ticket = new Ticket();
						ticket.setTickedid(rs.getInt("tickedid"));
						ticket.setTicketnum(rs.getInt("ticketnum"));
						ticket.setTrainno(rs.getString("trainno"));
						ticket.setOrigin(rs.getString("origin"));
						ticket.setDestination(rs.getString("destination"));
						ticket.setPrice(rs.getDouble("price"));
						
						ticketlist.add(ticket);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		return ticketlist;
	
	}

	public int QueryTicNum(String origin,String destination) {
		Connection conn = null;
		PreparedStatement ps = null;
		int num = 0;
		try {
			Context context = new InitialContext();
			DataSource datasource = (DataSource)context.lookup("java:comp/env/jdbc/maventest");
			conn = datasource.getConnection();
		} catch (NamingException | SQLException e) {
			e.printStackTrace();
		}
		
		if (null != conn) {
			ResultSet rs=null;
			try {
				ps = conn.prepareStatement("select count(*) num from ticket where origin=? and destination=? ");
				ps.setString(1, origin);
				ps.setString(2, destination);
			
				// 执行
				rs=ps.executeQuery();
				
				while (rs.next()) {
					num = rs.getInt("num");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
		}
		return num;
	
	}
}
